package com.wpw.dream.util.excel;

import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * ClassName : ExcelWriterUtil <br/>
 * Function : 导出excel的工具类<br/>
 * Date : 2016年8月8日 下午3:11:16 <br/>
 * @author KaiweiXiang
 * @version  
 * @see 	 
 */
public class ExcelWriterUtil {
	private final static String TMP = "/tmp/";
	
	private int rowIndex = 0;

	public String writerToExcel(String[] header,List<List<String>> datas, String sheetName,String fileName) throws Exception{
		if(fileName.endsWith(".xls")){
			return writerToExcel2003(header, datas, sheetName, fileName);
		}else if(fileName.endsWith(".xlsx")){
			return writerToExcel2007(header, datas, sheetName, fileName);
		}
		return null;
	}
	
	private String writerToExcel2003(String[] header,List<List<String>> datas, String sheetName,String fileName) throws Exception{
		String filePath = TMP + fileName;
		
		//判断文件夹是否存在，如果不存在则创建文件夹
		this.createFolder(filePath);
		
		//生成新文件
		HSSFWorkbook workbook = null;
		if(rowIndex == 0){
			workbook = new HSSFWorkbook(); ////生成新文件
		}else{
			workbook = getWorkBook(filePath); // 获取原来的文件
		}
		
		//生产sheet
		HSSFSheet sheet = workbook.getSheet(sheetName);
		if(sheet == null){
			sheet = workbook.createSheet(sheetName);
		}
		
		return writerExcelFile(workbook, sheet, header, datas, filePath);
	}
	
	private String writerToExcel2007(String[] header,List<List<String>> datas, String sheetName,String fileName) throws Exception{
		String filePath = TMP + fileName;
		
		//判断文件夹是否存在，如果不存在则创建文件夹
		this.createFolder(filePath);
		
		//生成新文件
		XSSFWorkbook workbook = null;
		if(rowIndex == 0){
			workbook = new XSSFWorkbook(); ////生成新文件
		}else{
			workbook = getWorkBook2007(filePath); // 获取原来的文件
		}
		//生产sheet
		XSSFSheet sheet = workbook.getSheet(sheetName);
		if(sheet == null){
			sheet = workbook.createSheet(sheetName);
		}
		
		return writerExcelFile(workbook, sheet, header, datas, filePath);
	}
	
	private String writerExcelFile(Workbook workbook,Sheet sheet,String[] header,List<List<String>> datas,String filePath) throws Exception{
		Map<Integer, Integer> autoWidthMap = this.autoSetColumnWidth(header, datas);
		
		//输入表头
		if(rowIndex == 0 && header != null && header.length > 0){
			this.createTitle(sheet, header,autoWidthMap, this.createStyle1(workbook));
		}
		
		//将数据写入
		this.appendGenerate2003(workbook, sheet, datas);
		
		//本地服务器写入Excel文件
		this.writeFile(workbook,filePath);
		
		return filePath;
	}
	
	/**
	 * 创建标题栏
	 * @param sheet 
	 * @param header 标题
	 * @param columWidthMap 每格宽度
	 */
	private void createTitle(Sheet sheet,String[] header,Map<Integer, Integer> autoWidthMap,CellStyle style){
		// 冻结第一行
//		sheet.createFreezePane(0, 1, 0, 1);

		Row headRow = sheet.createRow(this.rowIndex++);
		
		// 创建行，0表示第一行（本例是excel的标题）
		headRow.setHeight((short) (400));
		
		// 循环excel的标题
		for (int j = 0; j < header.length; j++) {
			
			
			// 使用行对象创建列对象，0表示第1列
			Cell cell = headRow.createCell(j);
			// cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			
			// 设置列宽
			Integer width = autoWidthMap.get(Integer.valueOf(j));
			if (width == null || width > 255 * 256) {
				width = 255 * 255;
			}
			sheet.setColumnWidth(j, width);
						
			if (header[j] != null) {
				// 将创建好的样式放置到对应的单元格中
				cell.setCellValue(header[j]);// 为标题中的单元格设置值
				// 添加样式
				cell.setCellStyle(style);
			} else {
				cell.setCellValue("-");
			}
		}
	}
	
	private void appendGenerate2003(Workbook workbook,Sheet sheet,List<List<String>> datas) {
		if(datas == null || datas.size() == 0){
			return;
		}
		
		// 生成并设置另一个样式
		CellStyle style2 = this.createStyle2(workbook);
			
		Row row = null;
		List<String> rowList = null;
		for(int i=0;i<datas.size();i++){
			rowList = datas.get(i);
			row = sheet.createRow(this.rowIndex++);
			
			// 遍历某一行的结果
			for (int n = 0; n < rowList.size(); n++) {
				// 使用行创建列对象
				Cell cell = row.createCell(n);
				cell.setCellStyle(style2);
				if (rowList.get(n) != null) {
					cell.setCellValue((String) rowList.get(n).toString());
				} else {
					cell.setCellValue("");
				}
			}
		}
	}
	
    //从已存在的文件中中获取HSSFWorkbook对象
	private HSSFWorkbook getWorkBook(String excelFileName) throws IOException{
		InputStream input = new FileInputStream(excelFileName);
        POIFSFileSystem fs = new POIFSFileSystem(input);
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        return wb;
	}
	
	private XSSFWorkbook getWorkBook2007(String excelFileName) throws IOException{
		InputStream input = new FileInputStream(excelFileName);
        XSSFWorkbook wb = new XSSFWorkbook(input);
        return wb;
	}
	//创建文件夹
	private void createFolder(String filePath){
		String folderPath = filePath.substring(0,filePath.lastIndexOf("/"));
		File folder = new File(folderPath);
		if(!folder.exists()){
			folder.mkdirs();
		}
	}
	/**
	 * 将Excel写到本地目录
	 * @param workbook Excel工作簿
	 * @param localFilePath 本地服务器路径
	 */
	private void writeFile(Workbook workbook,String localFilePath) throws Exception{
		BufferedOutputStream bos = null;
		try {
			bos = new BufferedOutputStream(new FileOutputStream(localFilePath));
			workbook.write(bos);
			bos.flush();
		} catch (FileNotFoundException e) {
			throw e;
		} catch (IOException e) {
			throw e;
		} finally {
			if(bos != null){
				try {
					bos.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
	}
	
	/**
	 * 自动设置列宽
	 */
	private Map<Integer, Integer> autoSetColumnWidth(String[] header, List<List<String>> data) {
		Map<Integer, Integer> map = new HashMap<Integer, Integer>();
		if (header != null) {
			for (int i = 0; i < header.length; i++) {
				map.put(i, header[i].getBytes().length * 1 * 256);
			}
		}
		if (data != null) {
			for (List<String> results : data) {
				for (int i = 0; i < results.size(); i++) {
					if (results.get(i) != null) {
						int result = results.get(i).getBytes().length * 1 * 256;
						if (map.get(i) < result) {
							map.put(i, result);
						}
					} else {
						int result = 0;
						if (map.get(i) < result) {
							map.put(i, result);
						}
					}
				}
			}
		}
		for(Integer key : map.keySet()){
			if(map.get(key) != null && map.get(key) > 20000){
				map.put(key,20000);
			}
		}
		return map;
	}
	private CellStyle createStyle1(Workbook workbook){
		// 创建样式对象
		CellStyle style = workbook.createCellStyle();
		// 设置这些样式
		style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

		// 生成一个字体
		Font font = workbook.createFont();
		font.setColor(HSSFColor.BLACK.index);
		font.setFontHeightInPoints((short) 12);
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

		// 把字体应用到当前的样式
		style.setFont(font);
		
		return style;
	}
	
	private CellStyle createStyle2(Workbook workbook){
		// 创建样式对象
		CellStyle style2 = workbook.createCellStyle();
		// 设置这些样式
		style2.setFillForegroundColor(HSSFColor.WHITE.index);
		style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

		// 生成另一个字体
		Font font2 = workbook.createFont();
		font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);

		// 把字体应用到当前的样式
		style2.setFont(font2);
		
		return style2;
	}
}
